set hive.exec.dynamic.partition = true;
set hive.exec.dynamic.partition.mode = 'nonstrict';
set hive.exec.max.dynamic.partitions.pernode = 200;
set hive.exec.max.dynamic.partitions = 200;

insert overwrite table jms_dm.dm_waybill_collect_intime
select a.taking_date
       , a.waybill
       , a.is_interrupted
       , a.truncated_time
       , a.receive_city_id
       , a.receive_city
       , a.region_code
       , a.region_name
       , a.receive_proxy_code
       , a.receive_proxy
       , a.receive_franchisee_code
       , a.receive_franchisee
       , a.receive_network_code
       , a.receive_network_name
       , a.actual_collect_code
       , a.actual_collect_name
       , a.plan_collect_code
       , a.plan_collect_name
       , a.actual_center_code
       , a.actual_center_name
       , a.collect_proxy_code
       , a.collect_proxy
       , a.is_diff_proxy
       , a.actual_collect_time
       , a.actual_send_time
       , a.actual_collect_arr_time
       , a.plan_collect_arr_time
       , a.actual_center_arr_time
       , a.hand_over_latest
       , a.is_arr_collect_intime
       , a.is_arr_center_intime
       , a.hand_over_intime
       , a.collect_responsible_code
       , a.collect_responsible
       , a.hand_over_responsible_code
       , a.hand_over_responsible
       , a.type
       , a.order_source
       , a.goods_type_code
       , a.goods_type_name
       , a.end_code
       , a.end_name
       , a.plan_arr_date
       , a.plan_center_arr_time
       , a.is_over
       , a.shipment_no
       , a.customer_code
       , a.total_days_t
       , a.center_planned_departure
       , a.is_intime
       , a.express_type_code
       , a.express_type_name
       , a.last_area_code
       , a.last_area_name
       , a.last_city_code
       , a.last_city_name
       , a.last_agent_code
       , a.last_agent_name
       , a.first_nodal_send_time
       , a.first_nodal_arrival_time
       , a.first_center_send_time
       , a.first_center_arrival_time
       , a.if_yiqin_late
       , a.if_route_late
       , a.if_center_send_timely
       , a.customer_name
       , a.first_nodal_agent_code
       , a.first_nodal_agent_name
       , a.first_nodal_fran_code
       , a.first_nodal_fran_name
       , a.shipment_shifts
       , a.taking_user_code
       , a.taking_user_name
       , a.actual_network_shipment_no
       , a.actual_nodal_shipment_no
       , a.first_network_plan_send_time
       , a.first_nodal_plan_send_time
       , a.hand_over_duty_flag
       , dim_network.area_code          as receive_area_code
       , dim_network.area_name          as receive_area_name
       , bagging.scan_time              as first_nodal_bagging_time
       , a.order_mark_en
       , a.order_mark_zh
       , case when direct_flag = 1 then 1
              when car_waybill_split_flag = 1 then 2
              else 0
         end  as white_list_type  -- 白名单类型(0无1直发2车件分离)
       , a.taking_date  as dt
from (
    select
          to_date(early_scan_time)      as taking_date                    --揽收日期
         ,waybill_no                    as waybill                        --运单号
         ,is_interrupted                as is_interrupted                 --是否截单0否1是2其他
         ,truncated_time                as truncated_time                 --截单时间
         ,receive_city_id               as receive_city_id                --收件城市id
         ,receive_city                  as receive_city                   --收件城市
         ,region_code                   as region_code                    --收件管理大区编码
         ,region_name                   as region_name                    --收件管理大区名称
         ,receive_proxy_code            as receive_proxy_code             --收件代理区编码
         ,receive_proxy                 as receive_proxy                  --收件代理区名称
         ,receive_franchisee_code       as receive_franchisee_code        --收件加盟商编码
         ,receive_franchisee            as receive_franchisee             --收件加盟商名称
         ,receive_network_code          as receive_network_code           --收件网点编码
         ,receive_network_name          as receive_network_name           --收件网点名称
         ,actual_collect_code           as actual_collect_code            --实际交件集散点编码
         ,actual_collect_name           as actual_collect_name            --实际交件集散点名称
         ,plan_collect_code             as plan_collect_code              --规划到达集散点编码
         ,plan_collect_name             as plan_collect_name              --规划到达集散点名称
         ,actual_center_code            as actual_center_code             --实际交件转运中心编码
         ,actual_center_name            as actual_center_name             --实际交件转运中心名称
         ,collect_proxy_code            as collect_proxy_code             --交件代理区编码
         ,collect_proxy                 as collect_proxy                  --交件代理区名称
         ,is_diff_proxy                 as is_diff_proxy                  --是否跨代理区交件
         ,early_scan_time               as actual_collect_time            --实际收件时间
         ,actual_send_time              as actual_send_time               --网点实际发件时间
         ,actual_collect_arr_time       as actual_collect_arr_time        --实际交件集散点时间
         ,null                          as plan_collect_arr_time          --规划到达集散点时间
         ,actual_center_arr_time        as actual_center_arr_time         --实际首中心到车时间
         ,null                          as hand_over_latest               --交接最晚到车时间
         ,null                          as is_arr_collect_intime          --到达集散是否准点
         ,null                          as is_arr_center_intime           --到达集散是否准点
         ,null                          as hand_over_intime               --交接是否准点
         ,null                          as collect_responsible_code       --交件不准点责任所属机构编码
         ,null                          as collect_responsible            --交件不准点责任所属机构
         ,null                          as hand_over_responsible_code     --交接不准点责任所属机构编码
         ,null                          as hand_over_responsible          --交接不准点责任所属机构
         ,type                          as type                           --类型,1:无三段码2:无路由3:错发4规划问题5正常6其他
         ,order_source                  as order_source                   --订单来源1紫金山2逍遥峰3桃花岛4其他
         ,goods_type_code               as goods_type_code                --物品类型编码
         ,goods_type_name               as goods_type_name                --物品类型名称
         ,last_network_code             as end_code                       --目的网点编码
         ,last_network_name             as end_name                       --目的网点名称
         ,null                          as plan_arr_date                  --规划到件日期
         ,null                          as plan_center_arr_time           --规划首中心到车时间
         ,null                          as is_over
         ,actual_center_shipment_no     as shipment_no                    --实际首中心到件任务号     (20220903新增)
         ,customer_code                 as customer_code                  --客户编号                 (20220903新增)
         ,null                          as total_days_t                   --T+x时效                  (20220903新增)
         ,null                          as center_planned_departure       --中心规划发车时间-1小时   (20220903新增)
         ,null                          as is_intime                      --时效是否准点             (20220903新增)
         ,express_type_code             as express_type_code              --产品类型编码             (20221103新增)
         ,express_type_name             as express_type_name              --产品类型名称             (20221103新增)
         ,last_area_code                as last_area_code                 --目的区/县编码            (20221113新增)
         ,last_area_name                as last_area_name                 --目的区/县名称            (20221113新增)
         ,last_city_code                as last_city_code                 --目的城市编码             (20221113新增)
         ,last_city_name                as last_city_name                 --目的城市名称             (20221113新增)
         ,last_agent_code               as last_agent_code                --目的代理区编码           (20221113新增)
         ,last_agent_name               as last_agent_name                --目的代理区名称           (20221113新增)
         ,first_nodal_send_time         as first_nodal_send_time          --始发集散发件扫描时间     (20221113新增)
         ,first_nodal_arrival_time      as first_nodal_arrival_time       --始发集散到件扫描时间     (20221113新增)
         ,first_center_send_time        as first_center_send_time         --始发转运中心发件扫描时间 (20221113新增)
         ,first_center_arrival_time     as first_center_arrival_time      --始发转运中心到件扫描时间 (20221113新增)
         ,if_yiqin_late                 as if_yiqin_late                  --是否疫情件               (20221213新增)
         ,null                          as if_route_late                  --路由晚点                 (20221213新增)
         ,null                          as if_center_send_timely          --是否交件晚点及时发出1:是,0:否(20221213新增)
         ,customer_name                 as customer_name                  --客户名称
         ,first_nodal_agent_code        as first_nodal_agent_code         --实际交件集散点代理区编码
         ,first_nodal_agent_name        as first_nodal_agent_name         --实际交件集散点代理区名称
         ,first_nodal_fran_code         as first_nodal_fran_code          --实际交件集散点加盟商编码
         ,first_nodal_fran_name         as first_nodal_fran_name          --实际交件集散点加盟商名称
         ,actual_center_shipment_shifts as shipment_shifts                --实际交件支线任务班次 20230420新增
         ,taking_user_code              as taking_user_code               --揽收业务员 20230516
         ,taking_user_name              as taking_user_name               --揽收业务员 20230516
         ,actual_network_shipment_no    as actual_network_shipment_no     --网点发件任务单
         ,actual_nodal_shipment_no      as actual_nodal_shipment_no       --集散到件发件任务单
         ,null                          as first_network_plan_send_time   --始发网点规划发车时间
         ,null                          as first_nodal_plan_send_time     --始发集散规划发车时间
         ,null                          as hand_over_duty_flag            --不准点责任标识
         ,order_mark_en                 as order_mark_en                  --订单标签英文
         ,order_mark_zh                 as order_mark_zh                  --订单标签中文
         ,car_waybill_split_flag        as car_waybill_split_flag         --车件分离标识(1是0否)
         ,direct_flag                   as direct_flag                    --直发标识(1是0否)
         -- ,to_date(early_scan_time)      as dt                             --揽收日期
    from jms_tmp.dwd_first_route_for_submit
    where type in (1,2,3)  --三段码问题、规划问题、自收自派问题
    union all
    select
          to_date(early_scan_time)      as taking_date                    --揽收日期
         ,waybill_no                    as waybill                        --运单号
         ,is_interrupted                as is_interrupted                 --是否截单0否1是2其他
         ,truncated_time                as truncated_time                 --截单时间
         ,receive_city_id               as receive_city_id                --收件城市id
         ,receive_city                  as receive_city                   --收件城市
         ,region_code                   as region_code                    --收件管理大区编码
         ,region_name                   as region_name                    --收件管理大区名称
         ,receive_proxy_code            as receive_proxy_code             --收件代理区编码
         ,receive_proxy                 as receive_proxy                  --收件代理区名称
         ,receive_franchisee_code       as receive_franchisee_code        --收件加盟商编码
         ,receive_franchisee            as receive_franchisee             --收件加盟商名称
         ,receive_network_code          as receive_network_code           --收件网点编码
         ,receive_network_name          as receive_network_name           --收件网点名称
         ,actual_collect_code           as actual_collect_code            --实际交件集散点编码
         ,actual_collect_name           as actual_collect_name            --实际交件集散点名称
         ,plan_collect_code             as plan_collect_code              --规划到达集散点编码
         ,plan_collect_name             as plan_collect_name              --规划到达集散点名称
         ,actual_center_code            as actual_center_code             --实际交件转运中心编码
         ,actual_center_name            as actual_center_name             --实际交件转运中心名称
         ,collect_proxy_code            as collect_proxy_code             --交件代理区编码
         ,collect_proxy                 as collect_proxy                  --交件代理区名称
         ,is_diff_proxy                 as is_diff_proxy                  --是否跨代理区交件
         ,early_scan_time               as actual_collect_time            --实际收件时间
         ,actual_send_time              as actual_send_time               --网点实际发件时间
         ,actual_collect_arr_time       as actual_collect_arr_time        --实际交件集散点时间
         ,null                          as plan_collect_arr_time          --规划到达集散点时间
         ,actual_center_arr_time        as actual_center_arr_time         --实际首中心到车时间
         ,null                          as hand_over_latest               --交接最晚到车时间
         ,null                          as is_arr_collect_intime          --到达集散是否准点
         ,null                          as is_arr_center_intime           --到达集散是否准点
         ,null                          as hand_over_intime               --交接是否准点
         ,null                          as collect_responsible_code       --交件不准点责任所属机构编码
         ,null                          as collect_responsible            --交件不准点责任所属机构
         ,null                          as hand_over_responsible_code     --交接不准点责任所属机构编码
         ,null                          as hand_over_responsible          --交接不准点责任所属机构
         ,type                          as type                           --类型,1:无三段码2:无路由3:错发4规划问题5正常6其他
         ,order_source                  as order_source                   --订单来源1紫金山2逍遥峰3桃花岛4其他
         ,goods_type_code               as goods_type_code                --物品类型编码
         ,goods_type_name               as goods_type_name                --物品类型名称
         ,last_network_code             as end_code                       --目的网点编码
         ,last_network_name             as end_name                       --目的网点名称
         ,null                          as plan_arr_date                  --规划到件日期
         ,null                          as plan_center_arr_time           --规划首中心到车时间
         ,null                          as is_over
         ,actual_center_shipment_no     as shipment_no                    --实际首中心到件任务号     (20220903新增)
         ,customer_code                 as customer_code                  --客户编号                 (20220903新增)
         ,null                          as total_days_t                   --T+x时效                  (20220903新增)
         ,null                          as center_planned_departure       --中心规划发车时间-1小时   (20220903新增)
         ,null                          as is_intime                      --时效是否准点             (20220903新增)
         ,express_type_code             as express_type_code              --产品类型编码             (20221103新增)
         ,express_type_name             as express_type_name              --产品类型名称             (20221103新增)
         ,last_area_code                as last_area_code                 --目的区/县编码            (20221113新增)
         ,last_area_name                as last_area_name                 --目的区/县名称            (20221113新增)
         ,last_city_code                as last_city_code                 --目的城市编码             (20221113新增)
         ,last_city_name                as last_city_name                 --目的城市名称             (20221113新增)
         ,last_agent_code               as last_agent_code                --目的代理区编码           (20221113新增)
         ,last_agent_name               as last_agent_name                --目的代理区名称           (20221113新增)
         ,first_nodal_send_time         as first_nodal_send_time          --始发集散发件扫描时间     (20221113新增)
         ,first_nodal_arrival_time      as first_nodal_arrival_time       --始发集散到件扫描时间     (20221113新增)
         ,first_center_send_time        as first_center_send_time         --始发转运中心发件扫描时间 (20221113新增)
         ,first_center_arrival_time     as first_center_arrival_time      --始发转运中心到件扫描时间 (20221113新增)
         ,if_yiqin_late                 as if_yiqin_late                  --是否疫情件               (20221213新增)
         ,null                          as if_route_late                  --路由晚点                 (20221213新增)
         ,null                          as if_center_send_timely          --是否交件晚点及时发出1:是,0:否(20221213新增)
         ,customer_name                 as customer_name                  --客户名称
         ,first_nodal_agent_code        as first_nodal_agent_code         --实际交件集散点代理区编码
         ,first_nodal_agent_name        as first_nodal_agent_name         --实际交件集散点代理区名称
         ,first_nodal_fran_code         as first_nodal_fran_code          --实际交件集散点加盟商编码
         ,first_nodal_fran_name         as first_nodal_fran_name          --实际交件集散点加盟商名称
         ,actual_center_shipment_shifts as shipment_shifts                --实际交件支线任务班次 20230420新增
         ,taking_user_code              as taking_user_code               --揽收业务员 20230516
         ,taking_user_name              as taking_user_name               --揽收业务员 20230516
         ,actual_network_shipment_no    as actual_network_shipment_no     --网点发件任务单
         ,actual_nodal_shipment_no      as actual_nodal_shipment_no       --集散到件发件任务单
         ,null                          as first_network_plan_send_time   --始发网点规划发车时间
         ,null                          as first_nodal_plan_send_time     --始发集散规划发车时间
         ,null                          as hand_over_duty_flag            --不准点责任标识
         ,order_mark_en                 as order_mark_en                  --订单标签英文
         ,order_mark_zh                 as order_mark_zh                  --订单标签中文
         ,car_waybill_split_flag        as car_waybill_split_flag         --车件分离标识(1是0否)
         ,direct_flag                   as direct_flag                    --直发标识(1是0否)
         -- ,to_date(early_scan_time)      as dt                             --揽收日期
    from jms_tmp.dwd_first_route_city_for_submit
    where type = 4  --同城件数据打标
    union all
    select
          to_date(early_scan_time)       as taking_date                    --揽收日期
         ,waybill_no                    as waybill                        --运单号
         ,is_interrupted                as is_interrupted                 --是否截单0否1是2其他
         ,truncated_time                as truncated_time                 --截单时间
         ,receive_city_id               as receive_city_id                --收件城市id
         ,receive_city                  as receive_city                   --收件城市
         ,region_code                   as region_code                    --收件管理大区编码
         ,region_name                   as region_name                    --收件管理大区名称
         ,receive_proxy_code            as receive_proxy_code             --收件代理区编码
         ,receive_proxy                 as receive_proxy                  --收件代理区名称
         ,receive_franchisee_code       as receive_franchisee_code        --收件加盟商编码
         ,receive_franchisee            as receive_franchisee             --收件加盟商名称
         ,receive_network_code          as receive_network_code           --收件网点编码
         ,receive_network_name          as receive_network_name           --收件网点名称
         ,actual_collect_code           as actual_collect_code            --实际交件集散点编码
         ,actual_collect_name           as actual_collect_name            --实际交件集散点名称
         ,plan_collect_code             as plan_collect_code              --规划到达集散点编码
         ,plan_collect_name             as plan_collect_name              --规划到达集散点名称
         ,actual_center_code            as actual_center_code             --实际交件转运中心编码
         ,actual_center_name            as actual_center_name             --实际交件转运中心名称
         ,collect_proxy_code            as collect_proxy_code             --交件代理区编码
         ,collect_proxy                 as collect_proxy                  --交件代理区名称
         ,is_diff_proxy                 as is_diff_proxy                  --是否跨代理区交件
         ,early_scan_time               as actual_collect_time            --实际收件时间
         ,actual_send_time              as actual_send_time               --网点实际发件时间
         ,actual_collect_arr_time       as actual_collect_arr_time        --实际交件集散点时间
         ,null                          as plan_collect_arr_time          --规划到达集散点时间
         ,actual_center_arr_time        as actual_center_arr_time         --实际首中心到车时间
         ,null                          as hand_over_latest               --交接最晚到车时间
         ,null                          as is_arr_collect_intime          --到达集散是否准点
         ,null                          as is_arr_center_intime           --到达中心是否准点
         ,null                          as hand_over_intime               --交接是否准点
         ,null                          as collect_responsible_code       --交件不准点责任所属机构编码
         ,null                          as collect_responsible            --交件不准点责任所属机构
         ,null                          as hand_over_responsible_code     --交接不准点责任所属机构编码
         ,null                          as hand_over_responsible          --交接不准点责任所属机构
         ,type                          as type                           --类型,1:无三段码2:无路由3:错发4规划问题5正常6其他
         ,order_source                  as order_source                   --订单来源1紫金山2逍遥峰3桃花岛4其他
         ,goods_type_code               as goods_type_code                --物品类型编码
         ,goods_type_name               as goods_type_name                --物品类型名称
         ,last_network_code             as end_code                       --目的网点编码
         ,last_network_name             as end_name                       --目的网点名称
         ,null                          as plan_arr_date                  --规划到件日期
         ,null                          as plan_center_arr_time           --规划首中心到车时间
         ,null                          as is_over
         ,actual_center_shipment_no     as shipment_no                    --实际首中心到件任务号     (20220903新增)
         ,customer_code                 as customer_code                  --客户编号                 (20220903新增)
         ,null                          as total_days_t                   --T+x时效                  (20220903新增)
         ,null                          as center_planned_departure       --中心规划发车时间-1小时   (20220903新增)
         ,null                          as is_intime                      --时效是否准点             (20220903新增)
         ,express_type_code             as express_type_code              --产品类型编码             (20221103新增)
         ,express_type_name             as express_type_name              --产品类型名称             (20221103新增)
         ,last_area_code                as last_area_code                 --目的区/县编码            (20221113新增)
         ,last_area_name                as last_area_name                 --目的区/县名称            (20221113新增)
         ,last_city_code                as last_city_code                 --目的城市编码             (20221113新增)
         ,last_city_name                as last_city_name                 --目的城市名称             (20221113新增)
         ,last_agent_code               as last_agent_code                --目的代理区编码           (20221113新增)
         ,last_agent_name               as last_agent_name                --目的代理区名称           (20221113新增)
         ,first_nodal_send_time         as first_nodal_send_time          --始发集散发件扫描时间     (20221113新增)
         ,first_nodal_arrival_time      as first_nodal_arrival_time       --始发集散到件扫描时间     (20221113新增)
         ,first_center_send_time        as first_center_send_time         --始发转运中心发件扫描时间 (20221113新增)
         ,first_center_arrival_time     as first_center_arrival_time      --始发转运中心到件扫描时间 (20221113新增)
         ,if_yiqin_late                 as if_yiqin_late                  --是否疫情件               (20221213新增)
         ,null                          as if_route_late                  --路由晚点                 (20221213新增)
         ,null                          as if_center_send_timely          --是否交件晚点及时发出1:是,0:否(20221213新增)
         ,customer_name                 as customer_name                  --客户名称
         ,first_nodal_agent_code        as first_nodal_agent_code         --实际交件集散点代理区编码
         ,first_nodal_agent_name        as first_nodal_agent_name         --实际交件集散点代理区名称
         ,first_nodal_fran_code         as first_nodal_fran_code          --实际交件集散点加盟商编码
         ,first_nodal_fran_name         as first_nodal_fran_name          --实际交件集散点加盟商名称
         ,actual_center_shipment_shifts as shipment_shifts                --实际交件支线任务班次 20230420新增
         ,taking_user_code              as taking_user_code               --揽收业务员 20230516
         ,taking_user_name              as taking_user_name               --揽收业务员 20230516
         ,actual_network_shipment_no    as actual_network_shipment_no     --网点发件任务单
         ,actual_nodal_shipment_no      as actual_nodal_shipment_no       --集散到件发件任务单
         ,null                          as first_network_plan_send_time   --始发网点规划发车时间
         ,null                          as first_nodal_plan_send_time     --始发集散规划发车时间
         ,null                          as hand_over_duty_flag            --不准点责任标识
         ,order_mark_en                 as order_mark_en                  --订单标签英文
         ,order_mark_zh                 as order_mark_zh                  --订单标签中文
         ,car_waybill_split_flag        as car_waybill_split_flag         --车件分离标识(1是0否)
         ,direct_flag                   as direct_flag                    --直发标识(1是0否)
         -- ,to_date(early_scan_time)      as dt                          --揽收日期
    from jms_tmp.dwd_second_error_route_for_submit
    where type in (5,4)   --错发问题
    union all
    select
          to_date(early_scan_time)      as taking_date                    --揽收日期
         ,waybill_no                    as waybill                        --运单号
         ,is_interrupted                as is_interrupted                 --是否截单0否1是2其他
         ,truncated_time                as truncated_time                 --截单时间
         ,receive_city_id               as receive_city_id                --收件城市id
         ,receive_city                  as receive_city                   --收件城市
         ,region_code                   as region_code                    --收件管理大区编码
         ,region_name                   as region_name                    --收件管理大区名称
         ,receive_proxy_code            as receive_proxy_code             --收件代理区编码
         ,receive_proxy                 as receive_proxy                  --收件代理区名称
         ,receive_franchisee_code       as receive_franchisee_code        --收件加盟商编码
         ,receive_franchisee            as receive_franchisee             --收件加盟商名称
         ,receive_network_code          as receive_network_code           --收件网点编码
         ,receive_network_name          as receive_network_name           --收件网点名称
         ,actual_collect_code           as actual_collect_code            --实际交件集散点编码
         ,actual_collect_name           as actual_collect_name            --实际交件集散点名称
         ,plan_collect_code             as plan_collect_code              --规划到达集散点编码
         ,plan_collect_name             as plan_collect_name              --规划到达集散点名称
         ,actual_center_code            as actual_center_code             --实际交件转运中心编码
         ,actual_center_name            as actual_center_name             --实际交件转运中心名称
         ,collect_proxy_code            as collect_proxy_code             --交件代理区编码
         ,collect_proxy                 as collect_proxy                  --交件代理区名称
         ,is_diff_proxy                 as is_diff_proxy                  --是否跨代理区交件
         ,early_scan_time               as actual_collect_time            --实际收件时间
         ,actual_send_time              as actual_send_time               --网点实际发件时间
         ,actual_collect_arr_time       as actual_collect_arr_time        --实际交件集散点时间
         ,null                          as plan_collect_arr_time          --规划到达集散点时间
         ,actual_center_arr_time        as actual_center_arr_time         --实际首中心到车时间
         ,null                          as hand_over_latest               --交接最晚到车时间
         ,null                          as is_arr_collect_intime          --到达集散是否准点
         ,null                          as is_arr_center_intime           --到达中心是否准点
         ,null                          as hand_over_intime               --交接是否准点
         ,null                          as collect_responsible_code       --交件不准点责任所属机构编码
         ,null                          as collect_responsible            --交件不准点责任所属机构
         ,null                          as hand_over_responsible_code     --交接不准点责任所属机构编码
         ,null                          as hand_over_responsible          --交接不准点责任所属机构
         ,type                          as type                           --类型,1:无三段码2:无路由3:错发4规划问题5正常6其他
         ,order_source                  as order_source                   --订单来源1紫金山2逍遥峰3桃花岛4其他
         ,goods_type_code               as goods_type_code                --物品类型编码
         ,goods_type_name               as goods_type_name                --物品类型名称
         ,last_network_code             as end_code                       --目的网点编码
         ,last_network_name             as end_name                       --目的网点名称
         ,null                          as plan_arr_date                  --规划到件日期
         ,null                          as plan_center_arr_time           --规划首中心到车时间
         ,null                          as is_over
         ,actual_center_shipment_no     as shipment_no                    --实际首中心到件任务号     (20220903新增)
         ,customer_code                 as customer_code                  --客户编号                 (20220903新增)
         ,null                          as total_days_t                   --T+x时效                  (20220903新增)
         ,null                          as center_planned_departure       --中心规划发车时间-1小时   (20220903新增)
         ,null                          as is_intime                      --时效是否准点             (20220903新增)
         ,express_type_code             as express_type_code              --产品类型编码             (20221103新增)
         ,express_type_name             as express_type_name              --产品类型名称             (20221103新增)
         ,last_area_code                as last_area_code                 --目的区/县编码            (20221113新增)
         ,last_area_name                as last_area_name                 --目的区/县名称            (20221113新增)
         ,last_city_code                as last_city_code                 --目的城市编码             (20221113新增)
         ,last_city_name                as last_city_name                 --目的城市名称             (20221113新增)
         ,last_agent_code               as last_agent_code                --目的代理区编码           (20221113新增)
         ,last_agent_name               as last_agent_name                --目的代理区名称           (20221113新增)
         ,first_nodal_send_time         as first_nodal_send_time          --始发集散发件扫描时间     (20221113新增)
         ,first_nodal_arrival_time      as first_nodal_arrival_time       --始发集散到件扫描时间     (20221113新增)
         ,first_center_send_time        as first_center_send_time         --始发转运中心发件扫描时间 (20221113新增)
         ,first_center_arrival_time     as first_center_arrival_time      --始发转运中心到件扫描时间 (20221113新增)
         ,if_yiqin_late                 as if_yiqin_late                  --是否疫情件               (20221213新增)
         ,null                          as if_route_late                  --路由晚点                 (20221213新增)
         ,null                          as if_center_send_timely          --是否交件晚点及时发出1:是,0:否(20221213新增)
         ,customer_name                 as customer_name                  --客户名称
         ,first_nodal_agent_code        as first_nodal_agent_code         --实际交件集散点代理区编码
         ,first_nodal_agent_name        as first_nodal_agent_name         --实际交件集散点代理区名称
         ,first_nodal_fran_code         as first_nodal_fran_code          --实际交件集散点加盟商编码
         ,first_nodal_fran_name         as first_nodal_fran_name          --实际交件集散点加盟商名称
         ,actual_center_shipment_shifts as shipment_shifts                --实际交件支线任务班次 20230420新增
         ,taking_user_code              as taking_user_code               --揽收业务员 20230516
         ,taking_user_name              as taking_user_name               --揽收业务员 20230516
         ,actual_network_shipment_no    as actual_network_shipment_no     --网点发件任务单
         ,actual_nodal_shipment_no      as actual_nodal_shipment_no       --集散到件发件任务单
         ,first_network_plan_send_time  as first_network_plan_send_time   --始发网点规划发车时间
         ,first_nodal_plan_send_time    as first_nodal_plan_send_time     --始发集散规划发车时间
         ,null                          as hand_over_duty_flag            --不准点责任标识
         ,order_mark_en                 as order_mark_en                  --订单标签英文
         ,order_mark_zh                 as order_mark_zh                  --订单标签中文
         ,car_waybill_split_flag        as car_waybill_split_flag         --车件分离标识(1是0否)
         ,direct_flag                   as direct_flag                    --直发标识(1是0否)
         -- ,to_date(early_scan_time)      as dt                             --揽收日期
    from jms_tmp.dwd_lasted_route_for_submit
    where type = 6   --未到交接时间问题
    union all
    select
          to_date(early_scan_time)      as taking_date                    --揽收日期
         ,waybill_no                    as waybill                        --运单号
         ,is_interrupted                as is_interrupted                 --是否截单0否1是2其他
         ,truncated_time                as truncated_time                 --截单时间
         ,receive_city_id               as receive_city_id                --收件城市id
         ,receive_city                  as receive_city                   --收件城市
         ,region_code                   as region_code                    --收件管理大区编码
         ,region_name                   as region_name                    --收件管理大区名称
         ,receive_proxy_code            as receive_proxy_code             --收件代理区编码
         ,receive_proxy                 as receive_proxy                  --收件代理区名称
         ,receive_franchisee_code       as receive_franchisee_code        --收件加盟商编码
         ,receive_franchisee            as receive_franchisee             --收件加盟商名称
         ,receive_network_code          as receive_network_code           --收件网点编码
         ,receive_network_name          as receive_network_name           --收件网点名称
         ,actual_collect_code           as actual_collect_code            --实际交件集散点编码
         ,actual_collect_name           as actual_collect_name            --实际交件集散点名称
         ,plan_collect_code             as plan_collect_code              --规划到达集散点编码
         ,plan_collect_name             as plan_collect_name              --规划到达集散点名称
         ,actual_center_code            as actual_center_code             --实际交件转运中心编码
         ,actual_center_name            as actual_center_name             --实际交件转运中心名称
         ,collect_proxy_code            as collect_proxy_code             --交件代理区编码
         ,collect_proxy                 as collect_proxy                  --交件代理区名称
         ,is_diff_proxy                 as is_diff_proxy                  --是否跨代理区交件
         ,early_scan_time               as actual_collect_time            --实际收件时间
         ,actual_send_time              as actual_send_time               --网点实际发件时间
         ,actual_collect_arr_time       as actual_collect_arr_time        --实际交件集散点时间
         ,plan_collect_arr_time         as plan_collect_arr_time          --规划到达集散点时间
         ,actual_center_arr_time        as actual_center_arr_time         --实际首中心到车时间
         ,hand_over_latest              as hand_over_latest               --交接最晚到车时间
         ,is_arr_collect_intime         as is_arr_collect_intime          --到达集散是否准点
         ,is_arr_center_intime          as is_arr_center_intime           --到达中心是否准点
         ,hand_over_intime              as hand_over_intime               --交接是否准点
         ,null                          as collect_responsible_code       --交件不准点责任所属机构编码
         ,null                          as collect_responsible            --交件不准点责任所属机构
         ,hand_over_responsible_code    as hand_over_responsible_code     --交接不准点责任所属机构编码
         ,hand_over_responsible         as hand_over_responsible          --交接不准点责任所属机构
         ,type                          as type                           --类型,1:无三段码2:无路由3:错发4规划问题5正常6其他
         ,order_source                  as order_source                   --订单来源1紫金山2逍遥峰3桃花岛4其他
         ,goods_type_code               as goods_type_code                --物品类型编码
         ,goods_type_name               as goods_type_name                --物品类型名称
         ,last_network_code             as end_code                       --目的网点编码
         ,last_network_name             as end_name                       --目的网点名称
         ,plan_arr_date                 as plan_arr_date                  --规划到件日期
         ,plan_center_arr_time          as plan_center_arr_time           --规划首中心到车时间
         ,is_over                       as is_over
         ,actual_center_shipment_no     as shipment_no                    --实际首中心到件任务号     (20220903新增)
         ,customer_code                 as customer_code                  --客户编号                 (20220903新增)
         ,total_days_t                  as total_days_t                   --T+x时效                  (20220903新增)
         ,center_planned_departure      as center_planned_departure       --中心规划发车时间-1小时   (20220903新增)
         ,is_intime                     as is_intime                      --时效是否准点             (20220903新增)
         ,express_type_code             as express_type_code              --产品类型编码             (20221103新增)
         ,express_type_name             as express_type_name              --产品类型名称             (20221103新增)
         ,last_area_code                as last_area_code                 --目的区/县编码            (20221113新增)
         ,last_area_name                as last_area_name                 --目的区/县名称            (20221113新增)
         ,last_city_code                as last_city_code                 --目的城市编码             (20221113新增)
         ,last_city_name                as last_city_name                 --目的城市名称             (20221113新增)
         ,last_agent_code               as last_agent_code                --目的代理区编码           (20221113新增)
         ,last_agent_name               as last_agent_name                --目的代理区名称           (20221113新增)
         ,first_nodal_send_time         as first_nodal_send_time          --始发集散发件扫描时间     (20221113新增)
         ,first_nodal_arrival_time      as first_nodal_arrival_time       --始发集散到件扫描时间     (20221113新增)
         ,first_center_send_time        as first_center_send_time         --始发转运中心发件扫描时间 (20221113新增)
         ,first_center_arrival_time     as first_center_arrival_time      --始发转运中心到件扫描时间 (20221113新增)
         ,if_yiqin_late                 as if_yiqin_late                  --是否疫情件               (20221213新增)
         ,if_route_late                 as if_route_late                  --路由晚点                 (20221213新增)
         ,if_center_send_timely         as if_center_send_timely          --是否交件晚点及时发出1:是,0:否(20221213新增)
         ,customer_name                 as customer_name                  --客户名称
         ,first_nodal_agent_code        as first_nodal_agent_code         --实际交件集散点代理区编码
         ,first_nodal_agent_name        as first_nodal_agent_name         --实际交件集散点代理区名称
         ,first_nodal_fran_code         as first_nodal_fran_code          --实际交件集散点加盟商编码
         ,first_nodal_fran_name         as first_nodal_fran_name          --实际交件集散点加盟商名称
         ,actual_center_shipment_shifts as shipment_shifts                --实际交件支线任务班次 20230420新增
         ,taking_user_code              as taking_user_code               --揽收业务员 20230516
         ,taking_user_name              as taking_user_name               --揽收业务员 20230516
         ,actual_network_shipment_no    as actual_network_shipment_no     --网点发件任务单
         ,actual_nodal_shipment_no      as actual_nodal_shipment_no       --集散到件发件任务单
         ,first_network_plan_send_time  as first_network_plan_send_time   --始发网点规划发车时间
         ,first_nodal_plan_send_time    as first_nodal_plan_send_time     --始发集散规划发车时间
         ,null                          as hand_over_duty_flag            --不准点责任标识 0截单后揽收,1有揽未发,2网点发车晚,3网点到集散运输晚,4网点到中心运输晚,5集散发车晚,6集散到中心运输晚
         ,order_mark_en                 as order_mark_en                  --订单标签英文
         ,order_mark_zh                 as order_mark_zh                  --订单标签中文
         ,car_waybill_split_flag        as car_waybill_split_flag         --车件分离标识(1是0否)
         ,direct_flag                   as direct_flag                    --直发标识(1是0否)
         -- ,to_date(early_scan_time)      as dt                             --揽收日期
    from jms_tmp.dwd_result_route_for_submit
    where type = 0 --应交接票数
) a
    left join jms_dim.dim_network_whole_massage dim_network
        on a.receive_network_code = dim_network.code
    left join(
        select
            waybill_no
            ,scan_site_code
            ,scan_time
            ,row_number() over (partition by waybill_no, scan_site_code order by scan_time ) as rn
        from jms_dwd.dwd_tab_barscan_bagging_base_hi
        where dt between date_add('{{ execution_date | cst_ds }}', -1)||'-00' and date_add('{{ execution_date | cst_ds }}',1)||'-06'
    ) bagging
        on a.waybill = bagging.waybill_no
            and a.actual_collect_code = bagging.scan_site_code
            and bagging.rn = 1
where a.receive_proxy_code is not null
distribute by dt,pmod(hash(rand()),100)
;